package com.mypog;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MySQLDb {

	String myDriver = "com.mysql.jdbc.Driver";
	String myUrl = "jdbc:mysql://mysql-laevakesed.jelastic.dogado.eu/laevakesed";
	//String myUrl = "jdbc:mysql://localhost/laevakesed";
	
	public String getSqlQuery(String query, String column, String column1) {
		List<String> list;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;

		try {
			Class.forName(myDriver);
			
			conn = DriverManager.getConnection(myUrl, "laevakesed",	"laevakesed");

			// create the java statement
			st = conn.createStatement();

			// execute the query, and get a java resultset
			rs = st.executeQuery(query);		

			list = new ArrayList<String>();

			// iterate through the java resultset
			while (rs.next()) {
				list.add(rs.getString(column));
				list.add(rs.getString(column1));
			}
			
			return listToString(list);
		} catch (Exception e) {
			return e.getMessage();
		} finally {
			closeQuietly(conn, st, rs);
		}
	}

	public String getSqlOneValue(String query, String column) {
		List<String> list;		

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;

		try {
			Class.forName(myDriver);

			conn = DriverManager.getConnection(myUrl, "laevakesed",
					"laevakesed");

			// create the java statement
			st = conn.createStatement();

			// execute the query, and get a java resultset
			rs = st.executeQuery(query);

			list = new ArrayList<String>();

			// iterate through the java resultset
			while (rs.next()) {
				list.add(rs.getString(column));
				}
			return listToString(list);			
		} catch (Exception e) {return e.getMessage();} 
		finally {
			closeQuietly(conn, st, rs);
			}
	}
	
	// Returned data as a String.
	private String listToString(List<String> list) {
		String[] arrayString;
		String data = "";
		int j = 0;
		
		arrayString = new String[list.size()];

		// list -> array -> String
		for (String s : list) {
			arrayString[j] = s;
			j++;
		}
		for (int i = 0; i < arrayString.length; i++) {
			if (i == arrayString.length - 1) {
				data += arrayString[i];
			} else {
				data += arrayString[i] + ",";
			}
		}		
		return data;		
	}

	public void setSqlQyery(String query) {
		Connection conn = null;
		Statement st = null;

		try {
			Class.forName(myDriver);
			conn = DriverManager.getConnection(myUrl, "laevakesed",
					"laevakesed");

			// create the java statement
			st = conn.createStatement();

			// execute the query
			st.executeUpdate(query);

		} catch (Exception e) {
			System.out.println("Got an exception! ");
			System.out.println(e.getMessage());
		} finally {	
			closeQuietly(conn, st, null);
			}
	}
	
	// Close connections.
	public void closeQuietly(Connection conn, Statement st, ResultSet rs) {
		if (rs != null)	try {rs.close();} catch (SQLException e) {}
		if (st != null)	try {st.close();} catch (SQLException e) {}
		if (conn != null) try {conn.close();} catch (SQLException e) {}
	}

}